################
#PSRM: Explaining Support for Redistribution: Social Insurance Systems and Fairness
#
#Observational Data
#Part III: Immigration and Stability
#
#Verena Fetscher
#July 2022
####################


rm(list=ls())

##########################
#Immigration
##########################

load("DataFile_02_TaxBenefit.Rda")

#Import data
#https://data.oecd.org/migration/foreign-born-population.htm
#accessed 11-2018

immigration<-read.csv("DP_LIVE_17112017110618032.csv", header = TRUE, sep = ",", quote = "\"",
                      dec = ".")
names(immigration) <- c("LOCATION","INDICATOR","SUBJECT","MEASURE",
                        "FREQUENCY","TIME","Value","Flag.Codes")

table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Loop through each country
##########################


##########################
#Austria
##########################

cntry<-"AT"
location<-"AUT"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2008]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Belgium
##########################

cntry<-"BE"
location<-"BEL"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Switzerland
##########################

cntry<-"CH"
location<-"CHE"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Germany
##########################

cntry<-"DE"
location<-"DEU"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Denmark
##########################

cntry<-"DK"
location<-"DNK"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Spain
##########################

cntry<-"ES"
location<-"ESP"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE



##########################
#Finland
##########################

cntry<-"FI"
location<-"FIN"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]


table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#France
##########################

cntry<-"FR"
location<-"FRA"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]


table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#GB
##########################

cntry<-"GB"
location<-"GBR"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]




table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Ireland
##########################

cntry<-"IE"
location<-"IRL"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Italy
##########################

cntry<-"IT"
location<-"ITA"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  (immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]+
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005])/2

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Netherlands
##########################

cntry<-"NL"
location<-"NLD"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Norway
##########################

cntry<-"NO"
location<-"NOR"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]




table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Portugal
##########################

cntry<-"PT"
location<-"PRT"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]



table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE
table(immigration$LOCATION)
#AUT BEL CHE CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IRL ITA LUX NLD 
#NOR POL PRT SVK SVN SWE


##########################
#Sweden
##########################

cntry<-"SE"
location<-"SWE"

data$immigration[data$cntry==cntry&data$essround==1]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2001]

data$immigration[data$cntry==cntry&data$essround==2]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2003]

data$immigration[data$cntry==cntry&data$essround==3]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2005]

data$immigration[data$cntry==cntry&data$essround==4]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2007]

data$immigration[data$cntry==cntry&data$essround==5]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2009]

data$immigration[data$cntry==cntry&data$essround==6]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2011]

data$immigration[data$cntry==cntry&data$essround==7]<-
  immigration$Value[immigration$LOCATION==location&immigration$TIME==2013]


data%>%
  group_by(cntry,essround)%>%
  summarise(mean=mean(immigration,is.na=T))


##########################
#Notes
##########################

#1) Value for Italy in 2004 is average of (2002+2006)/2
#2) Austria 2008 uses value for 2008 rather than previous year


##########################
#Save data
##########################

save(data,file="DataFile_03_Immigration.Rda")
##########################




##########################
#Income mobility
##########################

#http://ec.europa.eu/eurostat/web/income-and-living-conditions/data/database?p_p_id=NavTreeportletprod_WAR_NavTreeportletprod_INSTANCE_CEM7npyJJgVL&p_p_lifecycle=0&p_p_state=normal&p_p_mode=view&p_p_col_id=column-2&p_p_col_count=1
#http://appsso.eurostat.ec.europa.eu/nui/show.do?dataset=ilc_di30c&lang=en
#http://ec.europa.eu/eurostat/statistics-explained/index.php/EU_statistics_on_income_and_living_conditions_(EU-SILC)_methodology_-_distribution_of_income

#The Transitions of income within three years by decile refers to the 
#distribution (%) of persons by the income decile class they move to in 
#the survey year (t); shown separately for each income decile class during 
#the last three year (t-3).


#Import data
eurostat<-read.csv("ilc_di30c_1_Data.csv", header = TRUE, sep = ",", quote = "\"",
                   dec = ".")
names(eurostat) <- c("TIME","GEO","UNIT","TRANS3Y","QUANTILE","Value","Flag.and.Footnotes")

##########################
#Data recoding
##########################

#Factor to numeric
as.numeric.factor <- function(x) {as.numeric(levels(x))[x]}
as.character.factor <- function(x) {as.character(levels(x))[x]}
eurostat$ValueNum<-as.numeric(eurostat$Value)


#Extract data
x<-eurostat[eurostat$TRANS3Y=="No change"&eurostat$QUANTILE=="Total",] %>%
  group_by(GEO,TIME) %>%
  summarise(mean = mean(ValueNum, na.rm=T))



x$GEO<-as.factor(x$GEO)


table(x$GEO)
#Austria,Belgium,Denmark,Finland,France,
#Germany (until 1990 former territory of the FRG),Ireland,
#Italy,Netherlands,Norway,Portugal,Spain,Sweden,Switzerland,United Kingdom

table(data$cntry)
#AT BE CH DE DK ES FI FR GB IE IT NL NO PT SE


##########################
#Add data to ESS
##########################


##########################
#Austria
##########################

cntry<-"AT"
geo<-"Austria"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]



##########################
#Belgium
##########################

cntry<-"BE"
geo<-"Belgium"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]


##########################
#Switzerland
##########################

cntry<-"CH"
geo<-"Switzerland"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]



##########################
#Germany
##########################

cntry<-"DE"
geo<-"Germany (until 1990 former territory of the FRG)"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]



##########################
#Denmark
##########################

cntry<-"DK"
geo<-"Denmark"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]



##########################
#Spain
##########################

cntry<-"ES"
geo<-"Spain"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#Finland
##########################

cntry<-"FI"
geo<-"Finland"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#France
##########################

cntry<-"FR"
geo<-"France"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#GB
##########################

cntry<-"GB"
geo<-"United Kingdom"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#Ireland
##########################

cntry<-"IE"
geo<-"Ireland"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#Italy
##########################

cntry<-"IT"
geo<-"Italy"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#Netherlands
##########################

cntry<-"NL"
geo<-"Netherlands"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]



##########################
#Norway
##########################

cntry<-"NO"
geo<-"Norway"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#Portugal
##########################

cntry<-"PT"
geo<-"Portugal"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]




##########################
#Sweden
##########################

cntry<-"SE"
geo<-"Sweden"

data$mobility[data$cntry==cntry]<-mean(x$mean[x$GEO==geo],na.rm=T)
table(data$mobility[data$cntry==cntry])

data$mobility_year[data$cntry==cntry&data$essround==4]<-x$mean[x$GEO==geo&x$TIME==2008]
data$mobility_year[data$cntry==cntry&data$essround==5]<-x$mean[x$GEO==geo&x$TIME==2010]
data$mobility_year[data$cntry==cntry&data$essround==6]<-x$mean[x$GEO==geo&x$TIME==2012]
data$mobility_year[data$cntry==cntry&data$essround==7]<-x$mean[x$GEO==geo&x$TIME==2014]
##########################


##########################
#Check
##########################

group_by(data, 
         group = cntry) %>%
  summarise(meanMob = mean(mobility,na.rm=T))
##########################


##########################
#Save data
##########################

save(data,file="DataFile_04_Stability.Rda")
##########################



